# -*- coding: utf-8 -*-
import pandas as pd
import re
import os
import numpy as np

issue_name = ['王丹丹', '雷  敏', '郑屹凯', '陈  欣', '杨文梅', '刘  瑞', '陈  婷', '陈  洁', '刘士熠', '雷  敏2', '王奥雷(20230416)']

def extract_and_standardize_date(s):
    """去除括号和空格后，提取并返回日期字符串的后四位"""
    # 去除括号和所有空格
    cleaned_str = re.sub(r'[()\s]', '', s)
    
    # 查找六位或八位的数字序列
    match = re.search(r'\d{8}|\d{6}|\d{4}', cleaned_str)
    if match:
        date_str = match.group()
        # 返回日期的后四位
        date = date_str[-4:]
        if date == "2405":
            print(date_str)
            print(cleaned_str)
        return date
    else:
        print(f"{s}找不到日期")
        return None  # 如果找不到数字序列，则返回None

def get_name_money_list(excel_file, useName):
    print("正在处理",excel_file)
    # 使用pandas读取Excel文件
    xls = pd.ExcelFile(excel_file)
    name_list = []
    money_list = []
    sheet_num = len(xls.sheet_names)
    # print(f"该excel总共含有sheet页{sheet_num}")
    skip_count = 0
    # 遍历每一个sheet
    for sheet_name in xls.sheet_names:
        if sheet_name == '处方目录单价' or sheet_name == '模版' or sheet_name == '处方结算清单' or sheet_name.find("单价") != -1:
            # print(f"已排除{sheet_name}")
            skip_count = skip_count + 1
            continue
        # 读取当前sheet
        try:
            df = xls.parse(sheet_name, encoding='utf8')  # 假设Excel文件的编码为gbk，根据实际情况调整
        except Exception as e:
            print(f"读取sheet '{sheet_name}' 时发生错误: {e}")
            continue
        
        # 查找"合计"所在的行及其列索引
        try:
            # 先找到含有"合计"的单元格
            total_cell = df.stack().str.contains(useName, na=False)
            # 获取"合计"所在的行索引和列名
            total_positions = total_cell[total_cell].index.tolist()
        except Exception as e:
            print(f"在sheet '{sheet_name}' 中寻找{useName}时发生错误: {e}")
            continue
        try:
            for position in total_positions:
                row_idx, col_name = position
                # 获取"合计"所在的列数（基于0开始的索引）
                col_number = df.columns.get_loc(col_name)
                if sheet_name in issue_name:
                    value = df.iloc[row_idx, col_number+3]
                    if not pd.isnull(value):
                        name_list.append(sheet_name)
                        money_list.append(value)
                        continue 
                value = df.iloc[row_idx, col_number + 4]
                # print(sheet_name, value)
                if not pd.isnull(value):
                    name_list.append(sheet_name)
                    money_list.append(value)
                    continue
                value = df.iloc[row_idx, col_number + 5]
                if not pd.isnull(value):
                    name_list.append(sheet_name)
                    money_list.append(value)
                    continue
                print(sheet_name,"没计算")
        except Exception as e:
            print(f"在sheet '{sheet_name}' 中发生错误: {e}")
            # issue_name.append(sheet_name)
            continue
    # print(f"跳过{skip_count}页")
    # print("issue",issue_name)
    return name_list, money_list

if __name__ == "__main__":
    file_list = os.listdir("./account")
    count = 0
    total_list = []
    for file in file_list:
        file_path = os.path.join("./account", file)
        name_list, money_list = get_name_money_list(file_path, "合计")
        # print("总共计算了",len(money_list))
        # print(money_list)
        total = np.sum(money_list)
        print(f"{file_path}收入总和为：{total}")
        # print("-------------------------------------")
        total_list.append(total)
    for i in range(len(file_list)):
        print(f"{file_list[i]},{total_list[i]}")
        